﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Linq.Expressions;
using System.Data;
using System.Reflection;

namespace Utility
{
    public class LinqHelper<TDatabase> : IDisposable where TDatabase : DataContext, new()
    {

        public LinqHelper()
        {
            tDatabase = new TDatabase();
        }



        private TDatabase tDatabase;
        public LinqTransaction<TDatabase> Transaction;
        /// <summary>
        /// Begin new transaction if transaction is null. Default with IsLock mode is True.
        /// </summary>
        public void BeginTransaction()
        {
            if (tDatabase.Transaction == null || tDatabase.Transaction.Connection == null)
                Transaction = new LinqTransaction<TDatabase>(tDatabase, true);
        }
        /// <summary>
        /// Begin new transaction if transaction is null.
        /// </summary>
        /// <param name="isLock"></param>
        public void BeginTransaction(bool isLock)
        {
            if (tDatabase.Transaction == null || tDatabase.Transaction.Connection == null)
                Transaction = new LinqTransaction<TDatabase>(tDatabase, isLock);
        }




        /// <summary>
        /// Executes SQL queries directly on the database and return List of T object
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="strQuery"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(string strQuery, params object[] parameters) where T : class
        {
            return tDatabase.ExecuteQuery<T>(strQuery, parameters).ToList();
        }



        public static List<T> ReturnAllRows<T>() where T : class
        {
            return new TDatabase().GetTable<T>().ToList<T>();
        }

        /// <summary>
        /// Executes SQL commands directly on the database.
        /// </summary>
        /// <param name="strCommand"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteCommand(string strCommand, params object[] parameters)
        {
            return tDatabase.ExecuteCommand(strCommand, parameters);
        }

        /// <summary>
        /// Return a current datetime from server.
        /// </summary>
        /// <returns></returns>
        public DateTime GetServerDateTime()
        {
            return tDatabase.ExecuteQuery<DateTime>("SELECT GETDATE()").First();
            //this function in DataContext is protected.
            //return (DateTime) ExecuteMethodCall(this, (MethodInfo)MethodBase.GetCurrentMethod(), null).ReturnValue;
        }

        /// <summary>
        /// Return a collection of objects of a particular type, where the type is defined by the T parameter.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public IQueryable<T> GetTable<T>() where T : class
        {
            return tDatabase.GetTable<T>();
        }

        /// <summary>
        /// Get all data and return a list of objects of a particular type, where the type is defined by the T parameter.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetAll<T>() where T : class
        {
            return tDatabase.GetTable<T>().ToList();
        }

        /// <summary>
        /// Get limited data by paging.
        /// Return a list of objects of a particular type, where the type is defined by the T parameter.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<T> GetAll<T>(int pageNumber, int pageSize) where T : class
        {
            if (pageNumber < 1)
                throw new Exception("Page number is invalid.");
            return tDatabase.GetTable<T>().Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }

        /// <summary>
        /// Return a list of entities by filter (T parameter is a type of the entity).
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public List<T> Filter<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            return tDatabase.GetTable<T>().Where(predicate).ToList();
        }

        /// <summary>
        /// Return a list of entities by filter and paging (T parameter is a type of the entity).
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<T> Filter<T>(Expression<Func<T, bool>> predicate, int pageNumber, int pageSize)
        where T : class
        {
            if (pageNumber < 1)
                throw new Exception("Page number is invalid.");
            return tDatabase.GetTable<T>().Where(predicate).Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
        }

        /// <summary>
        /// Return an entity if exist, or not return Null. (T parameter is a type of the entity)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public T GetOne<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            return tDatabase.GetTable<T>().Where(predicate).SingleOrDefault();
        }
        /// <summary>
        /// Check an entity is exist in database. (T parameter is a type of the entity).
        /// Return True if the entity is exist.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public bool EntityExists<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            return tDatabase.GetTable<T>().Where(predicate).Count() > 0;
        }

        public static void MapAttributes<T, T2>(T source, T2 target)
        {
            foreach (var sprop in typeof(T).GetProperties())
            {
                foreach (var tprop in typeof(T2).GetProperties())
                {
                    if (sprop.Name == tprop.Name)
                    {
                        tprop.SetValue(target, sprop.GetValue(source, null), null);
                    }
                }
            }
        }

        public static List<T2> MapList<T, T2>(List<T> source) where T2 : new()
        {
            var result = new List<T2>();
            foreach (var o in source)
            {
                T2 t = new T2();
                MapAttributes<T, T2>(o, t);
                result.Add(t);
            }
            return result;
        }

        public static List<T> GenericMap<T>(IQueryable q, params string[] mappings) where T : new()
        {
            List<T> result = new List<T>();
            foreach (var i in q)
            {
                T obj = new T();
                foreach (var prop in i.GetType().GetProperties())
                {
                    string mapping = "";
                    foreach (var s in mappings)
                    {
                        string[] tokens = s.Split('>');
                        if (s != mappings.First() && tokens[0] == prop.Name)
                            mapping = tokens[1];
                    }
                    if (mapping == "") //Simple Mappings
                    {
                        foreach (var ps in prop.PropertyType.GetProperties())
                        {
                            foreach (var pt in typeof(T).GetProperties())
                            {
                                if (ps.Name == pt.Name && pt.PropertyType == ps.PropertyType)
                                {
                                    pt.SetValue(obj, ps.GetValue(prop.GetValue(i, null), null), null);
                                }
                            }
                        }
                    }
                    else //Second Level Mappings
                    {
                        PropertyInfo subObjProp = null;
                        PropertyInfo subSrcProp = prop;
                        foreach (var p in typeof(T).GetProperties())
                        {
                            if (p.Name == mapping)
                            {
                                subObjProp = p;
                            }
                        }
                        if (subObjProp == null) throw new Exception("LINQ ORM: Objects not compatible");

                        var subObj = Activator.CreateInstance(subObjProp.PropertyType);
                        var subSrc = prop.GetValue(i, null);

                        bool die = false;
                        foreach (var ps in subSrcProp.PropertyType.GetProperties())
                        {
                            foreach (var pt in subObjProp.PropertyType.GetProperties())
                            {
                                if (ps.Name == pt.Name && pt.PropertyType == ps.PropertyType)
                                {
                                    if (subSrc == null)
                                        die = true;
                                    else
                                        pt.SetValue(subObj, ps.GetValue(subSrc, null), null);
                                }
                            }
                        }
                        if (die == true) continue;

                        //Insert Tetriary IDs
                        foreach (var pt in subObjProp.PropertyType.GetProperties())
                        {
                            if (pt.GetValue(subObj, null) == null && !pt.PropertyType.IsSealed)
                            {
                                var tetriaryObj = Activator.CreateInstance(pt.PropertyType);
                                foreach (var p in subSrcProp.PropertyType.GetProperties())
                                {
                                    foreach (var p2 in pt.PropertyType.GetProperties())
                                    {
                                        if ((p2.Name == "ID") && p.Name == pt.PropertyType.Name + "ID")
                                        {
                                            p2.SetValue(tetriaryObj, p.GetValue(subSrc, null), null);
                                        }
                                    }
                                }
                                pt.SetValue(subObj, tetriaryObj, null);
                            }
                        }

                        subObjProp.SetValue(obj, subObj, null);
                    }
                }
                result.Add(obj);
            }
            return result;
        }


        /// <summary>
        /// Update all changes to database.
        /// Notes: Apply for all objects in reference to objects which get from database.
        /// </summary>
        public void UpdateChanges()
        {
            tDatabase.SubmitChanges();
        }

        /// <summary>
        /// Insert an entity into database (T parameter is a type of the entity).
        /// Notes: After inserting, this entity is mapping with database.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        public void Insert<T>(T entity) where T : class
        {
            tDatabase.GetTable<T>().InsertOnSubmit(entity);
            tDatabase.SubmitChanges();
        }
        /// <summary>
        /// Insert a list of entities (T parameter is a type of the entity).
        /// Notes: After inserting, this list is mapping with database.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList"></param>
        public void Insert<T>(List<T> entityList) where T : class
        {
            tDatabase.GetTable<T>().InsertAllOnSubmit(entityList);
            tDatabase.SubmitChanges();
        }

        /// <summary>
        /// Update an entity by expression (T parameter is a type of the entity).
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="predicate"></param>
        public void Update<T>(T entity, Expression<Func<T, bool>> predicate) where T : class
        {
            var t = tDatabase.GetTable<T>().Where(predicate).SingleOrDefault();
            if (t == null)
                throw new NullReferenceException("Error: Entity is not exist.");
            if (!t.Equals(entity))
                foreach (var p in t.GetType().GetProperties())
                    p.SetValue(t, p.GetValue(entity, null), null);

            tDatabase.SubmitChanges();
        }

        /// <summary>
        /// Delete data of one table has name is T parameter by expression.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        public void Delete<T>(Expression<Func<T, bool>> predicate) where T : class
        {
            var t = tDatabase.GetTable<T>().Where(predicate);
            if (t.Count() == 0) return;
            tDatabase.GetTable<T>().DeleteAllOnSubmit(t);
            tDatabase.SubmitChanges();
        }

        /// <summary>
        /// Delete all data of one table has name is T parameter.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public void DeleteAll<T>() where T : class
        {
            var t = tDatabase.GetTable<T>();
            if (t.Count() == 0) return;
            tDatabase.GetTable<T>().DeleteAllOnSubmit(t);
            tDatabase.SubmitChanges();
        }


        public void Dispose()
        {
            tDatabase = null;
        }
    }


}

